package com.bfg.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

@SuppressWarnings("serial")
public class TestDBServlet2 extends HttpServlet {
	private TestDBConnectionManager mgr;

	@Override
	public void init(ServletConfig config) throws ServletException {
		super.init(config);
		mgr = TestDBConnectionManager.getInstance();
	}

	@Override
	protected void service(HttpServletRequest req, HttpServletResponse res)
			throws ServletException, IOException {
		res.setContentType("text/html");
		PrintWriter out = res.getWriter();
		
		HttpSession session = req.getSession();
		session.removeAttribute("entryError");

		String action = req.getParameter("action");
		
		Connection conn = mgr.getConnection("mysql");
		if (conn == null) {
			out.println("can't get connection");
			return;
		}
		PreparedStatement prestmt;
		Statement stmt;
		ResultSet rs;
		ResultSetMetaData md;
		DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
		if (action.equals("select")) {
			try {
				stmt = conn.createStatement();
				rs = stmt.executeQuery("SELECT * FROM PRODUCT LIMIT 5");
				md = rs.getMetaData();
				out.println("<H3>TABLE PRODUCT<H3>");
				while (rs.next()) {
					out.println("<BR>");
					for (int i = 1; i < md.getColumnCount(); i++) {
						out.print(rs.getString(i) + ", ");
					}
				}
				stmt.close();
				rs.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (action.equals("insert")) {
			try {
				prestmt = conn
						.prepareStatement("INSERT INTO PRODUCT VALUES (?,?,?,?,?)");
				prestmt.setString(1, req.getParameter("isbn"));
				prestmt.setString(2, req.getParameter("title"));
				prestmt.setFloat(3, Float.valueOf(req.getParameter("price")));
				try {
					java.util.Date pubDate = df.parse(req
							.getParameter("pub_date"));
					prestmt.setDate(4, new java.sql.Date(pubDate.getTime()));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				prestmt.setString(5, req.getParameter("description"));
				if (prestmt.executeUpdate() == 1) {
					prestmt.close();
//					out.println("data has been inserted");
					session.setAttribute("entryError", "n");
					goToPage("WEB-INF/entryproduct.jsp", req, res);
				}
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		mgr.freeConnection("mysql", conn);
	}

	@Override
	public void destroy() {
		mgr.release();
		super.destroy();
	}
	
	// sending request to different page
	private void goToPage(String page, HttpServletRequest req,
			HttpServletResponse resp) throws ServletException, IOException {
		RequestDispatcher rd = req.getRequestDispatcher(page);
		if (rd != null)
			rd.forward(req, resp);

	}

}
